/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package EmpMonthlySal;

import SalaryComponent.EmpSalDetailsBean;
import SalaryComponent.EmpSalaryBean;
import SalaryComponent.SalaryComponentBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author Mukesh
 */
public class Query {

    static Connection con = null;

    public static Vector getMonthSalDetail(int id) throws SQLException {
        
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from EMPLOYEE_SALARY e left join (select EMP_SAL_DETAIL_ID,EMP_SAL_ID,SALARY_COMPONENT_ID,AMOUNT from EMP_SAL_DETAIL) s on e.emp_sal_id=s.emp_sal_id left join (select SALARY_COMPONENT_ID,COMPONENT_name as COMPONENT from SALARY_COMPONENT)sc on sc.SALARY_COMPONENT_ID=s.SALARY_COMPONENT_ID where user_id=?");
            pre.setInt(1, id);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                EmpSalaryBean esb = new EmpSalaryBean();
                EmpSalDetailsBean esdb = new EmpSalDetailsBean();
                SalaryComponentBean ecb = new SalaryComponentBean();
                
                esb.setEmpSalId(rs.getInt("EMP_SAL_ID"));
                esb.setUserId(rs.getInt("USER_ID"));
                esb.setTotalEarning(rs.getFloat("TOTAL_EARNING"));
                esb.setTotalDeduction(rs.getFloat("TOTAL_DEDUCTION"));
                esb.setTotalSal(rs.getFloat("TOTAL_SALARY"));
                esdb.setEmpSalDetId(rs.getInt("EMP_SAL_DETAIL_ID"));
                esdb.setAmount(rs.getFloat("AMOUNT"));
                esb.setEmpSalDetailsBean(esdb);
                ecb.setSalaryComponentId(rs.getInt("SALARY_COMPONENT_ID"));
                ecb.setSalaryComponent(rs.getString("COMPONENT"));
                esb.setSalaryComponentBean(ecb);
                
                list.add(esb);
            }
        } catch (Exception e) {
            System.out.println();
        } finally {
            con.close();
        }
        return list;
    }
    
    public static int add(MonthlySalBean msb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into MONTHLY_SALARY values(MONTHLYSALARYID.nextval,?,?,?,sysdate,?,?,?,?,?,sysdate,?,?)");
            pre.setLong(1, msb.getEmpId());
            pre.setInt(2, msb.getAmount());
            pre.setInt(3, msb.getWorkingDays());
            pre.setInt(4, msb.getPayMode());
            pre.setString(5, msb.getChequeNo());
            pre.setString(6, msb.getBank());
            pre.setString(7, msb.getBranch());
            pre.setInt(8, msb.getUserId());
            pre.setString(9, msb.getMonth());
            pre.setString(10, msb.getYear());
            i = pre.executeUpdate();
            

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    
}
